import requests
import pymysql
import lxml.html
import re
import time

etree = lxml.html.etree

#一次爬完案例数据基本信息

class ZhiCitySample:

    #初始化函数
    def __init__(self):
        self.header = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.111 Safari/537.36',
            'Content-type': 'text/html',
            'charset': 'utf-8'}
        self.url = "http://www.taskcity.com/portfolios"

    #获取文件信息
    def get_project(self):

        conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
        cur = conn.cursor()

        for i in range(1,1930):

            print("i:"+str(i))
            time.sleep(30)

            response = requests.get(self.url,params={"page":str(i)},headers = self.header)

            response.encoding="utf-8"

            if response.status_code == 200:
                html = etree.HTML(response.text)
                for j in range(1,16):

                    path = "/html/body/div[1]/div[2]/div/div[1]/div[%d]/div[2]/h3/a/@href" % (j)
                    link = html.xpath(path)
                    sid = re.findall(r"ios/(.*?)'",str(link))[0]
                    #print("sid:"+sid)

                    path = '/html/body/div[1]/div[2]/div/div[1]/div[%d]//h3[@class="portfolio_title"]/a/text()' % (j)
                    name = str(html.xpath(path)[0])
                    #print("name:"+name)

                    path = "/html/body/div[1]/div[2]/div/div[1]/div[%d]/div[2]/span/text()" % (j)
                    hot = str(html.xpath(path)[0])
                    #print("hot:"+hot)

                    nexturl = "http://www.taskcity.com" + link[0]
                    response = requests.get(nexturl,params={"page":str(i)},headers = self.header)
                    response.encoding="utf-8"
                    #print("link:"+link[0])

                    if response.status_code == 200:
                        nexthtml = etree.HTML(response.text)

                        path = '/html/body/div[2]//div[@class="user-id"]/text()'
                        uidstr = nexthtml.xpath(path)
                        uidlis = re.findall("\d+",str(uidstr))
                        if len(uidlis) > 0:
                            uid = uidlis[0]
                        else:
                            continue

                        path = "/html/body/div[2]/div[last()]/div[1]/div/text()"
                        detail = nexthtml.xpath(path)

                        #/html/body/div[2]/div[6]/div[1]/div/b[1]
                        timelist = re.findall("\d+",detail[3])
                        timenum = int(timelist[0])
                        if "年" in detail[3]:
                            timenum*=365
                        elif "月" in detail[3]:
                            timenum*=30
                        elif "周" in detail[3]:
                            timenum*=7
                        else:
                            print("日为单位")

                        lis = re.findall("\d+",detail[5].replace(",",""))
                        if len(lis) >= 2:
                            downprize = int(lis[0])
                            uprize = int(lis[1])
                            midprize = (uprize+downprize)/2
                        elif len(lis) == 1:
                            downprize = int(lis[0])
                            uprize = int(lis[0])
                            midprize = int(lis[0])
                        else:
                            downprize = 0
                            uprize = 0
                            midprize = 0

                        if midprize == 0 or timenum ==0:
                            average = 0
                        else:
                            average = midprize/timenum

                        try:
                            testsql = "select count(sid) from sample where sid = %s limit 1" % (sid)
                            cur.execute(testsql)
                            result = cur.fetchone()

                            if result[0] > 0:
                                sql = "update sample set uid = %s, name = '%s', uprize = %d, downprize = %d, midprize=%f, averageprofit = %f, time = %d, hot = %s where sid = %s" % (uid,name,uprize,downprize,midprize,average,timenum,hot,sid)
                            else:
                                sql = "insert into sample(sid,name,uid,uprize,downprize,midprize,averageprofit,time,hot) values(%s,'%s',%s,%d,%d,%f,%f,%d,%s)" % (sid,name,uid,uprize,downprize,midprize,average,timenum,hot)
                            print(sql)
                            cur.execute(sql)
                            conn.commit()
                        except:
                            print("sql error!")

                    #if j%5 == 0:
                    #    time.sleep(30)


        #关掉，关掉，数据库链接一定要记得关掉
        cur.close()
        conn.close()

if __name__ in "__main__":
    cls = ZhiCitySample()
    cls.get_project()
